import subprocess
import pandas as pd
import time
import speedtest
import re
import socket
from datetime import datetime
"""
openpyxl
"""
# 初始化DataFrame用于存储结果
columns = ['Time', 'Upload Speed (Mbps)', 'Download Speed (Mbps)', 'Latency (ms)', 'Jitter (ms)',
           'DNS Resolution Time (s)',
           'Signal Strength (%)', 'Packet Loss (0.01)']
df = pd.DataFrame(columns=columns)


# 用于测量网络延迟和丢包率的命令
def measure_ping(ip_address):
    output = subprocess.check_output(["ping", "-n", "15", ip_address], universal_newlines=True)
    # 提取丢包率
    packet_loss_match = re.search(r'丢失 = (\d+)', output)
    packet_loss = float(packet_loss_match.group(1)) / 15 if packet_loss_match else None  # 计算丢包率百分比

    # 提取平均延迟
    latency_match = re.search(r'平均 = (\d+)ms', output)
    latency = float(latency_match.group(1)) if latency_match else None

    # 提取抖动（最长延迟 - 最短延迟）
    min_latency_match = re.search(r'最短 = (\d+)ms', output)
    max_latency_match = re.search(r'最长 = (\d+)ms', output)
    min_latency = float(min_latency_match.group(1)) if min_latency_match else None
    max_latency = float(max_latency_match.group(1)) if max_latency_match else None
    jitter = max_latency - min_latency if min_latency and max_latency else None

    return latency, jitter, packet_loss


# 用于测量信号强度的命令
def measure_signal_strength():
    output = subprocess.check_output(["netsh", "wlan", "show", "interfaces"], universal_newlines=True)
    signal_strength_match = re.search(r'信号\s*:\s*(\d+)%', output)
    return int(signal_strength_match.group(1)) if signal_strength_match else None


# 用于测量DNS解析时间的命令
def measure_dns_resolution_time(domain):
    s = time.time()  # 记录解析开始时间
    try:
        socket.gethostbyname(domain)  # 执行DNS解析
    except socket.gaierror as e:
        print(f"无法解析域名 {domain}: {e}")
        return None
    e = time.time()  # 记录解析结束时间
    resolution_time = e - s  # 计算解析时间
    return resolution_time


"""
{'url': 'http://5g.shunicomtest.com:8080/speedtest/upload.php', 
'lat': '31.2000', 'lon': '121.5000', 'name': 'Shanghai', 
'country': 'China', 'cc': 'CN', 'sponsor': 'China Unicom 5G', 
'id': '24447', 'host': '5g.shunicomtest.com:8080',
 'd': 826.7543729167254, 'latency': 74.431}
 
 {'download': 42992655.34108085, 'upload': 71487643.3567702, 
 'ping': 51.402, 'server': {'url': 'http://cesu-nb.zjtelecom.com.cn:8080/speedtest/upload.php', 
 'lat': '29.8683', 'lon': '121.5440', 'name': 'NingBo', 'country': 'China', 'cc': 'CN', 
 'sponsor': '浙江电信', 'id': '59387', 'host': 'cesu-nb.zjtelecom.com.cn:8080', 
 'd': 914.7335238792995, 'latency': 51.402}, 'timestamp': '2024-12-12T02:34:08.600069Z', 
 'bytes_sent': 89505792, 'bytes_received': 53906996,
  'share': 'http://www.speedtest.net/result/17122639183.png',
'client': {'ip': '101.5.12.104', 'lat': '34.7732', 'lon': '113.722', 
'isp': 'China Education and Research Network Center',
 'isprating': '3.7', 'rating': '0', 'ispdlavg': '0', 
 'ispulavg': '0', 'loggedin': '0', 'country': 'CN'}}
"""

start_time = time.time()

# 主循环
for i in range(100):
    ms = time.time()
    now = datetime.now()
    print(f"Measurement {i + 1}/200")
    # 测量上传下载速度
    st = speedtest.Speedtest(secure=True)
    # 替换下面的 server_id 为你想要固定使用的服务器ID
    servers = ['59387']
    st.get_servers(servers)
    st.get_best_server()
    upload_speed = st.upload() / 10 ** 6  # Convert to Mbps
    download_speed = st.download() / 10 ** 6  # Convert to Mbps

    # 测量网络延迟\丢包率\抖动
    latency, jitter, packet_loss = measure_ping('www.iqiyi.com')

    # 测量DNS解析时间
    dns_resolution_time = measure_dns_resolution_time("learn.tsinghua.edu.cn")

    # 测量信号强度
    signal_strength = measure_signal_strength()

    # 添加一行数据
    new_row = pd.DataFrame({
        'Time': [now],
        'Upload Speed (Mbps)': [upload_speed],
        'Download Speed (Mbps)': [download_speed],
        'Latency (ms)': [latency],
        'Jitter (ms)': [jitter],
        'DNS Resolution Time (s)': [dns_resolution_time],
        'Signal Strength (%)': [signal_strength],
        'Packet Loss (0.01)': [packet_loss]
    })

    df = pd.concat([df, new_row], ignore_index=True)
    es = time.time()
    print(es - ms)
    time_to_wait = 60 - ((time.time() - start_time) % 60)
    time.sleep(time_to_wait)

# 将结果写入Excel文件
df.to_excel("network_metrics.xlsx", index=False)

# 打印结果
output = subprocess.check_output(["netsh", "wlan", "show", "interfaces"], universal_newlines=True)

with open("output.txt", 'w', encoding='utf-8') as file:
    file.write(output)
